import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelSplitterByRegion {

    public static void main(String[] args) {
        String inputFilePath = "D:\\path\\input.xlsx"; // 输入的Excel文件路径
        String outputFolder = "D:\\path\\";    // 输出文件夹
        int regionCodeColumnIndex = 0;       // 区划编码所在的列索引（0-based）

        try {
            splitExcelByRegionCode(inputFilePath, outputFolder, regionCodeColumnIndex);
            System.out.println("Excel文件拆分完成！");
        } catch (IOException e) {
            System.err.println("处理Excel文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 根据区划编码拆分Excel文件
     *
     * @param inputFilePath       输入文件路径
     * @param outputFolder        输出文件夹
     * @param regionCodeColumnIndex 区划编码列索引
     * @throws IOException 如果发生IO错误
     */
    public static void splitExcelByRegionCode(String inputFilePath, String outputFolder,
                                              int regionCodeColumnIndex) throws IOException {
        // 读取原始Excel文件
        try (FileInputStream fis = new FileInputStream(inputFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 假设处理第一个工作表

            // 存储不同区划的数据行
            Map<String, List<Row>> regionDataMap = new HashMap<>();

            // 读取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IllegalArgumentException("Excel文件没有表头行！");
            }

            // 遍历数据行（从第二行开始）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 获取区划编码
                Cell regionCodeCell = row.getCell(regionCodeColumnIndex);
                String regionCode = getCellValueAsString(regionCodeCell);

                // 按区划编码分组
                regionDataMap.computeIfAbsent(regionCode, k -> new ArrayList<>()).add(row);
            }

            // 为每个区划创建单独的Excel文件
            for (Map.Entry<String, List<Row>> entry : regionDataMap.entrySet()) {
                String regionCode = entry.getKey();
                List<Row> rows = entry.getValue();

                // 创建新的工作簿
                try (Workbook newWorkbook = new XSSFWorkbook()) {
                    Sheet newSheet = newWorkbook.createSheet("Sheet1");

                    // 复制表头
                    Row newHeaderRow = newSheet.createRow(0);
                    copyRow(headerRow, newHeaderRow, newWorkbook);

                    // 复制数据行
                    for (int i = 0; i < rows.size(); i++) {
                        Row newRow = newSheet.createRow(i + 1);
                        copyRow(rows.get(i), newRow, newWorkbook);
                    }

                    // 保存文件
                    String outputFilePath = outputFolder + "output_" + regionCode + ".xlsx";
                    try (FileOutputStream fos = new FileOutputStream(outputFilePath)) {
                        newWorkbook.write(fos);
                    }
                }
            }
        }
    }

    /**
     * 将单元格值转换为字符串
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 防止科学计数法
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    /**
     * 复制行数据
     */
    private static void copyRow(Row sourceRow, Row targetRow, Workbook targetWorkbook) {
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            if (sourceCell == null) continue;

            Cell targetCell = targetRow.createCell(i);
            CellStyle newCellStyle = targetWorkbook.createCellStyle();
            newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
            targetCell.setCellStyle(newCellStyle);

            switch (sourceCell.getCellType()) {
                case STRING:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
                    break;
                case NUMERIC:
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                    break;
                case BOOLEAN:
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                    break;
                case FORMULA:
                    targetCell.setCellFormula(sourceCell.getCellFormula());
                    break;
                default:
                    break;
            }
        }
    }
}